<?php
/*
 * Created on Aug 31, 2007 by Gabriel Pratt
 * For LN2_database
 */
 
 /*
  * takes a list of columns to select, a typed join statement, optional 
  * $column => $item where clause, and an optional typed $options statement
  * that allows the user to speficy things like how to order the results.
  * returns $mysql resultor die('Could not connect: ');
  * connects to a sql server and accesses the liquid nirogen database
	  
*/
			
function selectDatabase() {
	$link = @mysql_connect("localhost", "bot", "bot");
	mysql_select_db("murry_LN2_database") or die('Could not select database');
	
	return $link;
}
/*
 *takes a list of columns to select, a typed join statement, optional 
 *$column => $item where clause, and an optional typed $options statement
 *that allows the user to speficy things like how to order the results.
 *returns $mysql result
 */
function selectFromDatabase( $select,  $tables,  $where=0,  $options="" ) {
	//echo "Enters select from database";
	$selectStatement .= $select[0];
	unset($select[0]);
	foreach( $select as $column) {
		$selectStatement .= ", " . mysql_real_escape_string($column). "";  	
	}
	if ($where != 0){
		$whereStatement = generateWhereStatement($where);
	}
	$query = "SELECT $selectStatement FROM $tables $whereStatement $options";
	//echo $query. "\n </br>";
	
	$result = mysql_query($query) or die('Query failed in select from database: ' . mysql_error() . "Query: $query");
	//echo var_dump($result);
	return $result;
		
		
}
/*
 *$table is the table to insert values into
 *$values are one row of $keyscolumns) => $values(rows)
 *that are insterted into database.
 */
function insertIntoDatabase($table, $values) {
	return updateDatabaseGeneric(False, $table, $values);	
	
}
/*
 *$table is the table to insert values into
 *$values are $table is the table to insert values into
 *$values are one row of $keys(columns) => $values(rows)
 *is and array of where to change it, $keys(columns) => $values
 *that are insterted into database. one row of $keys(columns) => $values(rows)
 *is and array of where to change it, $keys(columns) => $values
 *that are insterted into database.
 */
function updateDatabase($table, $values, $where) {
	return updateDatabaseGeneric(True,$table, $values, $where);	
	
}
/* $statement defines if Update or INSERT 	
 *$table is the table to insert values into
 *$values are one row of $keys(columns) => $values(rows)
 *is and array of where to change it, $keys(columns) => $values
 *that are insterted into database.
*/
function updateDatabaseGeneric( $isUpdate,  $table,  $values,  $where=0) { 
	selectDatabase();
	$first = True;
	foreach ($values as $k => $v) {
		if(!$first)
			$updateStatement .= ", ";
		$updateStatement .= "$k='". mysql_real_escape_string($v) . "'";
		$first = False;
	}
	//echo print_r($where);
	if($where != 0) {
		$whereStatement = generateWhereStatement($where);
	}
	$statement = $isUpdate ? "UPDATE" : "INSERT ";
	$query = "$statement $table SET $updateStatement $whereStatement" ;
	//echo $query;
	$result = mysql_query($query) or die('Query failed update database: ' . mysql_error() . "Query: $query");
	//echo var_dump($result);
	return $result;
}
/*
 * @param $table specifices table to be removed from
 * @param $where specifices an array that will become the where statement.
 */
function removeFromDatabase($table, $where) {
	$whereStatement = generateWhereStatement($where);
	$query = "DELETE FROM $table $whereStatement";
	mysql_query($query) or die('Query failed remove from database: ' . mysql_error() . "Query: $query");;
}


//controller
//Gets information from a $result query and sorts information into a 2-d array
//This array consts of Array stores each feild and each feild stores data with in that feild.
function generateWhereStatement($where) {
	//echo empty($where);
	if(!empty($where)){
		$whereStatement = " WHERE ";
		$first = True;
		foreach ($where as $k=>$v) {
			if(!$first) 
				$whereStatement .= " AND ";
			$whereStatement .= "$k='" . mysql_real_escape_string($v) . "'";
			$first = False;
		}
	}
		return $whereStatement;
		
}
function getKeys($result) {
	
	$keys = array();
	while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
		foreach ($line as $key => $value) { 
			 $keys[$key][] = $value; //maps keys to a key and then the values with in that key.
		}
	}
	return $keys; 
}
	/*
	 * checks an individual frozen tube to see if it is 
	 * meets the search  requierments
	 * if true returns zero.
	 * 
	 * This method is very inifficent need to refactor.  
	 */
 function checkFrozenTube($frozenTubeID) {
 	$where['FrozenTube.id'] = $frozenTubeID;
 	
 	if ($_GET['CellTypeID'] !=  "") {
 		$where['FrozenTube.CellTypeID'] .= mysql_real_escape_string($_GET['CellTypeID']);
 		$hasValue = true;
 	}
 	if($_GET['ModifiedByID'] != "") {
 		$where['FrozenTube.ModifiedByID'] .= mysql_real_escape_string($_GET['ModifiedByID']);
 		$hasValue = true;
 	}
 	if($_GET['FrozenByID'] != "") {
 		$where['FrozenTube.FrozenByID'] .= mysql_real_escape_string($_GET['FrozenByID']);
 		$hasValue = true;
 	}

 	if(isset($_GET['ContainerID'])) {
 		$hasValue = true;
 		$where['FrozenTube.ContainerID'] .= mysql_real_escape_string($_GET['ContainerID']);
 	}
 	if($_GET['textSearch'] != "") {
 		$hasValue = true;
 		$textSearch = "'". mysql_real_escape_string($_GET['textSearch']) . "'";
  		$textSearchString = " AND (CellType.name REGEXP $textSearch OR CellType.Species REGEXP $textSearch OR CellType.Media REGEXP $textSearch OR CellType.Culture REGEXP $textSearch OR CellType.ThawTechnique REGEXP $textSearch OR FrozenTube.Note REGEXP $textSearch OR FrozenTube.PassageFrozen REGEXP $textSearch OR FrozenTube.NumberFrozen REGEXP $textSearch OR Modification.Name REGEXP $textSearch Or Modification.Description  REGEXP $textSearch OR Researcher.Name REGEXP $textSearch OR Researcher.Email REGEXP $textSearch)";
 	}
 	//echo $where;
 	if(!$hasValue) {
 		return false;
 	}
	$result = selectFromDatabase(array("FrozenTube.id"), 'FrozenTube JOIN CellType
 			ON(FrozenTube.CellTypeID=CellType.id) JOIN
 			Modification ON(FrozenTube.ModifiedByID=Modification.id)
 			JOIN Researcher ON (FrozenTube.FrozenByID=Researcher.id)
 			JOIN Container ON(FrozenTube.ContainerID=Container.id)', $where, $textSearchString);
 	if (hasResult($result))
 		return True;
 	return False;
 	
 }
 
 /*
  * Takes a result resource and checks to see if there
  * is any data in that resource.
  */
 function hasResult($result) {
 	if ($line = mysql_fetch_array($result))
 		return True;
 	return False;
 }
 
 
 function checkBox($boxID) {
 	$count = 0;
 	//Modifies get $get statement so queries in are generated properly.  If info is null doesn't go into where statement.'
 	if(isset($_GET['ContainerTypeID'])) {
 		//hacking where statement to include a join to get proper functionality
 		$hasContainerType = true;
 		$containerTypeid = $_GET['ContainerTypeID'];
 		unset($_GET['ContainerTypeID']); // Need to unset to get $where to work
 	}
 	if(isset($_GET['textSearch']) && $_GET['textSearch'] != "") {
 		$hasTextSearch=true;
 		$textSearch = $_GET['textSearch']; 		
 	}
 	unset($_GET['textSearch']); //This won't quite work because regexp might interpet some characters as special ones'
 	if($_GET['CellTypeID'] == "" || null) {
 		unset($_GET['CellTypeID']);
 	}
 	if($_GET['ModifiedByID'] == "" || null) {
 		unset($_GET['ModifiedByID']);
 	}
 	if($_GET['FrozenByID'] == "" || null) {
 		unset($_GET['FrozenByID']);
 	}
 	$where .= generateWhereStatement($_GET);
 	if($hasContainerType) { //and puttin it back after the where statement.
 		if(empty($where)) {
 			$where .= "WHERE";
 		} else {
 			$where .= "AND";
 		}
 		$where = "Container.ContainerTypeID='$containerTypeid' ";
 		$_GET['ContainerTypeID'] = $containerTypeid;
 	}
 	if($hasTextSearch) {
 		$_GET['textSearch'] = $textSearch ;
 		if(empty($where)) {
 			$where .="WHERE";
 		} else {
 			$where .="AND";
 		}
 		$textSearch ="'$textSearch'"; //parcing the string some how so it reads as a string instead of a regexp expression 
 		$where .= " (CellType.name  REGEXP $textSearch OR " . //Searches every item that has approprate text in the database
 				"CellType.Species REGEXP $textSearch OR " .
 				"CellType.Media REGEXP $textSearch OR CellType.Culture REGEXP $textSearch" .
 				" OR CellType.ThawTechnique REGEXP $textSearch " .
 				"OR FrozenTube.Note REGEXP $textSearch OR " .
 				"FrozenTube.PassageFrozen REGEXP $textSearch OR " .
 				"FrozenTube.NumberFrozen REGEXP $textSearch OR" .
 				" Modification.Name REGEXP $textSearch Or " .
 				"Modification.Description  REGEXP $textSearch OR " .
 				"Researcher.Name REGEXP $textSearch OR Researcher.Email REGEXP $textSearch)";
 		
 	}
 	if (empty($where)) {
 		$where = "WHERE ContainerID ='$boxID'";
 	} else {
 		$where .= "AND FrozenTube.ContainerID ='$boxID'";
 	}
 	$query = "SELECT count(FrozenTube.id) FROM FrozenTube JOIN CellType " .
 			"ON(FrozenTube.CellTypeID=CellType.id) JOIN " .
 			"Modification ON(FrozenTube.ModifiedByID=Modification.id) " .
 			"JOIN Researcher ON (FrozenTube.FrozenByID=Researcher.id)" .
 			"JOIN Container ON(FrozenTube.ContainerID=Container.id)  " . $where;
 	$result = mysql_query($query) or die ('Query failed checkbox: '  . " Query: $query \n </br>". mysql_error());
 	$line = mysql_fetch_array($result, MYSQL_ASSOC);
 	$count +=  $line['count(FrozenTube.id)'];
 	return $count;

 }


/*
 * Takes a single line(a tube)  of a result statement 
 * and returns a human readable view of exactly where 
 * in a box or cane that Tube is. 
 * numRows, numCols, LetterHeight, LetterWidth, and ContainerTypeID 
 * must all be set as globals before printLocation can be called.
 */
 
function printLocation($line) {	
	$location = $line['Location'];
	$containerID = $line['ContainerID'];
	unset($line['Location']);
	unset($line['ContainerID']);
	$query ="SELECT ContainerType.numRows, ContainerType.numCols, ContainerType.LetterHeight, ContainerType.LetterWidth " .
			"FROM ContainerType " .
			"WHERE ContainerType.id = (SELECT ContainerTypeID FROM Container WHERE Container.id=$containerID)";
	//echo print_r($line);
	//echo $query . "</br> \n ";
	$result = mysql_query($query) or die('Query failed in print location: ' . mysql_error . "Query: $query");
	$line =mysql_fetch_array($result, MYSQL_ASSOC);
	return convertToLocation($line, $location);
}

function convertToLetter($num) {
	$letterArray = array(1=> 'A' ,'B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
	return $letterArray[$num];
}

function getTubeLocation($id) {
	$query = "SELECT Container.Tank, Container.Tower, Container.Position, FrozenTube.Location, FrozenTube.ContainerID " .
			"FROM Container JOIN FrozenTube ON(Container.id=ContainerID)" .
			"WHERE Container.id=" .
			"(SELECT ContainerID FROM FrozenTube WHERE FrozenTube.id='" . mysql_real_escape_string($id) . "') AND FrozenTube.id='". mysql_real_escape_string($id) . "';";
	$result = mysql_query($query ) or die ('Query failed in get tube location: ' . "Query: $query" . mysql_error());
	$line = mysql_fetch_array($result, MYSQL_ASSOC);
	$position = printLocation($line);
	$location = "Tank: " . $line['Tank'] . " Tower: " . $line['Tower'] . " Box: " . $line['Position'] . " Position: " . $position;
	//concatanate container info
	//send line to printLocation;
	//concatanate
	return $location;
}
/*
 * Determens if a frozen tube is empty.  Returns true if true
 * takes line repsenting a frozen tube.
 */
function isEmpty($line) {
	
	if ($line['CellTypeID'] == getEmptyCellType())  
		return True;
	return False;
}
/*
 * Makes the varables in the user part of the liquid nitrogen database
 * static when being passed though different pages.
 */
function makeSearchLink($array, $includeContainer = true) {
	if(isset($array['ContainerID']) && $includeContainer) {
		$search = "ContainerID=" . $array['ContainerID'];
	}
	if($array['CellTypeID'] != "") {
		$search .= "&CellTypeID=" . $array['CellTypeID'];
				
	}
	if($array['ModifiedByID'] != "") {
		$search .= "&ModifiedByID=" . $array['ModifiedByID'];
	}
	if($array['FrozenByID'] != "") {
		$search .= "&FrozenByID=" . $array['FrozenByID'];
	}
	if(isset($array['textSearch'])) {
		$search .= "&textSearch=" . $array['textSearch'];
	}
	return $search;
}
function isChecked($sqlBool) {
	if ($sqlBool == 1) 
	{

		return true;
	} elseif($sqlBool == 0) {
	
		return false;
	}
}

function BoolTomysql($formattedBool) {
	
	return $mysqlBool;
}
function setEmptyTypes() {
	selectDatabase();
	$_SESSION['CellTypeID'] = getEmptyCellType();
	$_SESSION['ModifiedByID'] = getEmptyMod();
	$_SESSION['FrozenByID'] = getEmptyResearcher();
}

function getEmptyCellType() {
	$query = "SELECT id FROM CellType WHERE Name=\"Empty\"";
	return getIDFromResult(mysql_query($query));
	
}

function getEmptyMod() {
	$query = "SELECT id FROM Modification WHERE Name=\"WildType\"";
	return getIDFromResult(mysql_query($query));
}

function getEmptyResearcher() {
	$query = "SELECT id FROM Researcher WHERE Name=\"Nobody\"";
	return getIDFromResult(mysql_query($query));
}

function getIDFromResult($result) {
	$line = mysql_fetch_array($result, MYSQL_ASSOC);
	return $line['id'];
}

/*
 * checks to see if there is any data in a query.
 */
function queryExists($result) {
//	echo mysql_num_rows($result); 
	return (mysql_num_rows($result) >= 1);
}	

function curPageName() {
 return substr($_SERVER["SCRIPT_NAME"],strrpos($_SERVER["SCRIPT_NAME"],"/")+1);
}

/*
 * @param $value a value from the database
 * @param $key a column in the database
 * @param $table a table in the database
 * Checks to see in there is the value in that column of 
 * the table.  If there is returns true else returns false.
 */
function tableHasValue($column, $value, $table) {
	//echo "enters table has value";
	$result = selectFromDatabase(array($column), $table, array($column => $value));
	return queryExists($result);
}

//Takes a column ($key) a value in that column ($value) and a table and checks to see 
//That value exists in the FrozenTube table.  If it does not the value gets deleted from
//The specfied table
function deleteScript($key, $value, $table) {
	//echo "key:$key column:$column value:$value table: $table";
	//Special stuff for containers and container types because they don't act like mods and cell types'
	//checks all cells in a single tube for refrences to a type.
	if($table == "CellType") {
		$column = 'CellTypeID';
	} elseif($table== "Modification") {
		$column = 'ModifiedByID';
	} elseif($table=="Container") {
		$column = 'ContainerID';
	} elseif($table =="Researcher") {
		$column = 'FrozenByID';
	} elseif($table== "ContainerType") {
		$column = 'ContainerTypeID';
}
	
	if($table =='Container') {
		
		//gets all the cells from the container in question
		$result = selectFromDatabase(array('CellTypeID'), 'FrozenTube', array('ContainerID' => $value));
		while($line = mysql_fetch_array($result)) {
			if(!isEmpty($line)) { //checks to see if the container is empty
				return false;
			}
		}
	//Checks the list of containers for refrences to a type
	} elseif($table == 'ContainerType') {
		if(tableHasValue('ContainerTypeID', $value, 'Container')) {
			return false;
		}
	} elseif (tableHasValue($column, $value, 'FrozenTube')) {
		return false;
	}
		removeFromDatabase($table, array($key => $value));
		return true;
	

		
}
//Converts a yyyy-mm-dd into a mm/dd/yyyy formated date
function mysql2date ($mysqlDate){
	$yr = substr($mysqlDate, 0, 4);
	$month = substr($mysqlDate, 5, 2);
	$day = substr($mysqlDate, 8, 2);
	return $month . "/" . $day . "/" . $yr;
	
}
//Converts a mm/dd/yyyy formated date into a yyyy-mm-dd date
function date2mysql ($standardDate) {
	$month = substr($standardDate, 0 , 2);
	$day = substr($standardDate, 3, 2);
	$yr = substr($standardDate, 6, 4);
	return $yr . "-" . $month . "-" . $day;
}
function isAdmin() {
	return isset($_COOKIE['Admin']);
}
function isHelper() {
	return isset($_COOKIE['Helper']);
}
 function makeReadOnly() {
 	if(isset($_COOKIE['Guest'])) {
 		echo "readonly=\"readonly\"";
 	}
 }
 //Takes a table name and such as CellType and converts it into celltypeID
function tableToIDName($tableName){
	if($tableName == "CellType") {
		$table = 'CellTypeID';
	} elseif($tableName == "Modification") {
		$table = 'ModifiedByID';
	} elseif($tableName =="Container") {
		$table = 'ContainerID';
	} elseif($tableName =="Researcher") {
		$table = 'FrozenByID';
	} elseif($tableName == "ContainerType") {
		$table = 'ContainerTypeID';
	}
	return $table;
 }	
 /* for use with ViewGeneric because I was to stupid to have consistant varable names */
 function tableToTabeIDName($tableName) {
 	if($tableName == "CellType") {
		$table = 'CellTypeID';
	} elseif($tableName == "Modification") {
		$table = 'ModificationID';
	} elseif($tableName =="Researcher") {
		$table = 'ResearcherID';
 	}
 	return $table;
 }
 //Creates a cookie in an array starting with the zeroith element and incrementing by one.
  /*
  function cookieArray($cookieArray, $curPage,$delete=false) {
	$len = count($_SESSION['back']); //Always gets the last element of the array
 	
	$cookieLocation = "back". "[" . $len . "][$cookieArray]"; //If I don't do this php evalaulates the expression before it can be stored'
	setcookie("$cookieLocation" , "$curPage"); //Expires 1 hour after being set.
}
	*/
?>
